CREATE TABLE [dbo].[tb_TramitesResiduales]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Codigo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Tema] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TipoTramite] [int] NOT NULL,
[FechaInicio] [datetime] NULL,
[FechaFin] [datetime] NULL,
[Fojas] [int] NULL,
[Organismo] [int] NULL,
[Agente] [int] NULL,
[Norma] [int] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[generarCodigo] ON [dbo].[tb_TramitesResiduales] 
FOR INSERT 
AS

	DECLARE @ID int
	DECLARE @Codigo varchar(2)
	DECLARE @NuevoCodigo varchar(15)
	DECLARE @Anio varchar(4)
	DECLARE @TipoTramite INT

	SELECT @Anio=CONVERT(varchar(4), YEAR(inserted.FechaInicio)), @TipoTramite=inserted.TipoTramite, @ID=inserted.ID FROM inserted
	set @Codigo=(SELECT Codigo FROM dbo.tb_TiposTramitesResiduales WHERE ID=@TipoTramite)

	SET @NuevoCodigo=(Select CASE WHEN (MAX(SUBSTRING(Codigo,4,6) )) IS NOT NULL THEN 
	 @Codigo+'-'+right('000000' +  CAST( (MAX(SUBSTRING(Codigo,4,6) ) + 1) as varchar),6) +'-'+@Anio
	ELSE
	 @Codigo+'-'+'000001'+'-'+@Anio 
	END as NuevoCodigo
	  from tb_TramitesResiduales WHERE SUBSTRING(Codigo,11,13) = @Anio)
  
	UPDATE dbo.tb_TramitesResiduales SET Codigo=@NuevoCodigo WHERE ID=@ID

/****** Object:  Trigger [dbo].[agregarMovimiento]    Script Date: 12/08/2014 9:18:36 ******/
SET ANSI_NULLS ON
GO
ALTER TABLE [dbo].[tb_TramitesResiduales] ADD CONSTRAINT [PK__tb_Trami__3214EC276B611ABB] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_TramitesResiduales] WITH NOCHECK ADD CONSTRAINT [FK_tb_TramitesResiduales_tb_Agentes] FOREIGN KEY ([Agente]) REFERENCES [dbo].[tb_Agentes] ([idAgente])
GO
ALTER TABLE [dbo].[tb_TramitesResiduales] WITH NOCHECK ADD CONSTRAINT [FK_tb_TramitesResiduales_tb_Organismos] FOREIGN KEY ([Organismo]) REFERENCES [dbo].[tb_Organismos] ([idOrganismo])
GO
ALTER TABLE [dbo].[tb_TramitesResiduales] WITH NOCHECK ADD CONSTRAINT [FK_tb_TramitesResiduales_tb_TiposTramitesResiduales] FOREIGN KEY ([TipoTramite]) REFERENCES [dbo].[tb_TiposTramitesResiduales] ([ID])
GO
ALTER TABLE [dbo].[tb_TramitesResiduales] NOCHECK CONSTRAINT [FK_tb_TramitesResiduales_tb_Agentes]
GO
ALTER TABLE [dbo].[tb_TramitesResiduales] NOCHECK CONSTRAINT [FK_tb_TramitesResiduales_tb_Organismos]
GO
ALTER TABLE [dbo].[tb_TramitesResiduales] NOCHECK CONSTRAINT [FK_tb_TramitesResiduales_tb_TiposTramitesResiduales]
GO
